using SQLHeavy;
using Gee;
using Gtk;
using Meridian.Side;

namespace Meridian.Data {

public class Dao {
    public static string DATE_FORMAT = "%Y-%m-%d %H:%M";
    private const string FILE = "/home/duncan/Projects/meridian-browser/persistance.db";
    private Database database;
    
    public Dao () {
        if (!FileUtils.test (FILE, FileTest.IS_REGULAR)) {
            print ("Database %s does not exist", FILE);
        } 
        try {
	    	this.database = new Database (FILE, SQLHeavy.FileMode.WRITE);
	    	print ("Opened " + database.filename + "\n");
    	} catch (SQLHeavy.Error error) {
	    	print ("Failed to open " + FILE + " database" + error.message + "\n");
    	}
    }
    
    
    public QueryResult? select_records (DataTable table, Gee.List<DataOrder> order) { 
        
        //build ordering
        var builder = new StringBuilder ();
        var iterator = order.iterator ();
        while (iterator.has_next ()) {
	        iterator.next ();
            var entry = iterator.get ();
            builder.append (entry.to_string ());
            if(iterator.has_next ()) {
                builder.append (" THEN ");
            }
        }
        
        
        QueryResult results = null;
        try {
	        //prepare statement
	        Query query = this.database.prepare ("SELECT `*` FROM `bookmark`;");
	        //Query query = this.database.prepare ("SELECT * FROM :table ORDER BY :order");
	        print ("Selecting from " + database.filename + "\n");
	        //query.set_string (":table", table.to_string ());
	        //query.set_string (":order", builder.str);
	        
	        //execute
	        print (query.sql + "\n");
	        results = query.execute ();
        } catch (SQLHeavy.Error error) {
	    	print ("Failed to select: " + error.message + "\n");
    	}
        return results;
    }
    
    public void insert_record (DataTable table, Map<string, string> fields) {
        //build column and list strings
        debug("Inserting new record into " + table.to_string ());
        var column_builder = new StringBuilder ();
        var value_builder = new StringBuilder ();
        var iterator = fields.entries.iterator ();
        while (iterator.has_next ()) {
            iterator.next ();
            var entry = iterator.get ();
            column_builder.append (entry.key);
            value_builder.append ("'").append (entry.value).append ("'");
            if(iterator.has_next ()) {
                column_builder.append (", ");
                value_builder.append (", ");
            }
        }
        
        try {
	        //prepare statement
	        Query query = this.database.prepare ("INSERT INTO :table (:columns) VALUES (:values)");
	        query.set_string (":table", table.to_string ());
	        query.set_string (":columns", column_builder.str);
	        query.set_string (":values", value_builder.str);
	        
	        //execute
	        print (query.sql + "\n");
	        query.execute ();
        } catch (SQLHeavy.Error error) {
	    	print ("Failed to insert: " + error.message);
    	}
    }
    
    public void delete_record (DataTable table, Map<string, string> conditions) {
        //build conditionals
        var builder = new StringBuilder ();
        var iterator = conditions.entries.iterator ();
        while (iterator.has_next ()) {
	        iterator.next ();
            var entry = iterator.get ();
            builder.append (entry.key).append (" = '").append (entry.value).append ("'");
            if(iterator.has_next ()) {
                builder.append (" AND ");
            }
        }
        
        try {
	        //prepare statment
	        Query query = this.database.prepare ("DELETE FROM :table WHERE :condition");
	        query.set_string (":table", table.to_string ());
	        query.set_string (":conditions", builder.str);
	        
	        //execute
	        print (query.sql + "\n");
			query.execute ();
        } catch (SQLHeavy.Error error) {
	    	print ("Failed to delete: " + error.message);
    	}
    }
    
}
}